package loader.dewey.operations;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import loader.dewey.DeweyXmlElement;

import utils.database.DB;
import utils.database.DBStream;
import utils.timer.Timer;

public class DeweyOperations {
	
	private int document_id;
	
	public void delete() throws SQLException
	{
		String q = "DELETE FROM \"Elements\" WHERE SUBSTRING(dewey,0,13) IN (";
		
		for(int i = 0; i < 10; i++)
		{
			q += "'00000.0000" + i + ".',";
		}
		
		q += "'00000.00010.')";
		
		Statement s = DB.getConnection().createStatement();
		s.executeUpdate(q);
	}
	
	public void copyLastToFirst(String title)
	{
		
		try {
			
			Statement st;
			st = DB.getConnection().createStatement();			
			st.addBatch(shiftRightElements(selectFirstACT()));
			//st.addBatch	(insertAclElement(title));			
			Timer t = new Timer();
			t.start();
			st.executeBatch();			
			insertAclElement(title);
			t.stop();
			
			System.out.println("Fim >> " + t.timeElapsed() + " ms");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	
	private void insertAclElement(String title) throws SQLException {
		
		Statement s = DB.getConnection().createStatement();
		
		ResultSet r = s.executeQuery(selectLastAct());
		
		while(r.next())
		{
			DeweyXmlElement elem = new DeweyXmlElement();
			String dewey = r.getString("dewey");
			dewey = "00000.00005." + dewey.substring("00000.00005.".length());
			elem.setDewey(dewey);
			elem.setDocument_id(r.getInt("document_id"));
			elem.setPath_id(r.getInt("path_id"));
			if(r.getString("value").contains("ACT V"))
			{
				elem.setValue(title);
			}
			else
			{
				elem.setValue(DBStream.cleanup(r.getString("value")));
			}
			
			DBStream.addElement(elem.getSqlValueString());		
		}		
	}

	private String selectLastAct() {
		String q = "SELECT * FROM \"Elements\" WHERE SUBSTRING(dewey, 0, 13) = " + selectLastDeweyAct();
/*		System.out.println(q);
*/		return q;
	}

	private String shiftRightElements(String deweyDad)
	{
		String q = "UPDATE \"Elements\" SET dewey = " + incrementDewey() + 
		" WHERE dewey >= " + deweyDad ;
		
		return q;
	}
	

	private String shiftRightAttributes(String deweyDad)
	{
		String q = "UPDATE \"Elements\" SET dewey = " + incrementDewey() + 
		" WHERE dewey >= " + deweyDad + " char_length(dewey) >= char_length(" + deweyDad + ")";
		
		return q;
	}
	
	/**
	 * Selects the last ACT = ACT V
	 * @return
	 */
	private String selectLastDeweyAct()	
	{
		String q = "(SELECT dewey FROM \"Elements\" WHERE path_id = " +
		"(SELECT path_id FROM \"Paths\" WHERE path = 'PLAY/ACT/' LIMIT 1)" +
		"ORDER BY dewey desc LIMIT 1)";
		
		return q;
	}
	
	/**
	 * Increments only second position of the dewey
	 * @return
	 */
	private String incrementDewey()
	{		
		String q = "(SUBSTRING(dewey, 0,6) || '.'" +
				" || substring('00000' || CAST((CAST(SUBSTRING(dewey, 7,5) AS INTEGER) + 1) AS VARCHAR)" +
				" from (char_length(CAST((CAST(SUBSTRING(dewey, 7,5) AS INT) + 1) AS VARCHAR))+1) for 5) || '.'" +
				" || CAST(SUBSTRING(dewey, 13,10000) AS VARCHAR))";

			
		return q;
	}
	
	/**
	 * Select the first ACT
	 * @return The dewey column
	 */
	private String selectFirstACT()
	{
		String q = "(SELECT dewey FROM \"Elements\" WHERE path_id = " +
				"(SELECT path_id FROM \"Paths\" WHERE path = 'PLAY/ACT/' LIMIT 1)" +
				"ORDER BY DEWEY LIMIT 1)";		
		
		return q;
	}
	
	public static void main(String[] args)
	{
		DeweyOperations op = new DeweyOperations();
		op.copyLastToFirst("ACT 0-10");
	}

	void setDocument_id(int document_id) {
		this.document_id = document_id;
	}

	int getDocument_id() {
		return document_id;
	}
}
